package zy.dao.base.size.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.size.SizeDAO;
import zy.entity.base.size.T_Base_Size;
import zy.entity.base.size.T_Base_SizeList;
import zy.entity.base.size.T_Base_Size_Group;
@Repository
public class SizeDAOImpl extends BaseDaoImpl implements SizeDAO{
	@Override
	public Integer count(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_base_size t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.sz_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Base_Size> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object start = param.get("start");
		Object end = param.get("end");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sz_id,sz_code,sz_name");
		sql.append(" from t_base_size t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.sz_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by sz_id desc");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Base_Size> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Size.class));
		return list;
	}

	@Override
	public Integer queryByName(T_Base_Size size) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sz_id from T_Base_Size");
		sql.append(" where 1=1");
		if(null != size.getSz_name() && !"".equals(size.getSz_name())){
			sql.append(" and sz_name=:sz_name");
		}
		if(null != size.getSz_id() && size.getSz_id() > 0){
			sql.append(" and sz_id <> :sz_id");
		}
		sql.append(" and companyid=:companyid");
		sql.append(" limit 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(size),Integer.class);
			return id;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public T_Base_Size_Group queryGroupByID(Integer sz_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select szg_id,szg_code,szg_name,companyid from T_Base_Size_Group");
		sql.append(" where szg_id=:szg_id");
		sql.append(" limit 1");
		try{
			T_Base_Size_Group data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("szg_id", sz_id),new BeanPropertyRowMapper<>(T_Base_Size_Group.class));
			sql.setLength(0);
			sql.append("select szl_id,szl_sz_code,sz_name,szl_szg_code  ");
			sql.append(" from t_base_sizelist sl");
			sql.append(" join t_base_size s");
			sql.append(" on s.sz_code=sl.szl_sz_code");
			sql.append(" and s.companyid=sl.companyid");
			sql.append(" where szl_szg_code=:szg_code");
			sql.append(" and sl.companyid=:companyid");
			sql.append(" order by sl.szl_order");
			List<T_Base_SizeList> list = namedParameterJdbcTemplate.query(sql.toString(), new BeanPropertySqlParameterSource(data), new BeanPropertyRowMapper<>(T_Base_SizeList.class));
			data.setSizelist(list);
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public void saveGroup(Map<String,Object> map) {
		T_Base_Size_Group group = (T_Base_Size_Group)map.get("size_group");
		List<T_Base_SizeList> sizeList = (List<T_Base_SizeList>)map.get("size_list");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_two_code(max(szg_code+0)) from T_Base_Size_Group ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(group), String.class);
		group.setSzg_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO T_Base_Size_Group");
		sql.append(" (szg_code,szg_name,szg_number,companyid)");
		sql.append(" VALUES(:szg_code,:szg_name,:szg_number,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(group));
		for(T_Base_SizeList size:sizeList){
			size.setSzl_szg_code(group.getSzg_code());
		}
		sql.setLength(0);
		sql.append("INSERT INTO T_Base_SizeList");
		sql.append(" (szl_sz_code,szl_szg_code,szl_order,companyid)");
		sql.append(" VALUES(:szl_sz_code,:szl_szg_code,:szl_order,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(sizeList.toArray()));
	}

	@SuppressWarnings("unchecked")
	@Override
	public void updateGroup(Map<String,Object> map) {
		T_Base_Size_Group group = (T_Base_Size_Group)map.get("size_group");
		List<T_Base_SizeList> sizeList = (List<T_Base_SizeList>)map.get("size_list");
		StringBuffer sql = new StringBuffer("");
		sql.append(" update T_Base_Size_Group");
		sql.append(" set szg_name=:szg_name");
		sql.append(" where szg_id=:szg_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(group));
		sql.setLength(0);
		sql.append(" DELETE FROM T_Base_SizeList");
		sql.append(" WHERE szl_szg_code=:szg_code");
		sql.append(" and companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(group));
		sql.setLength(0);
		sql.append("INSERT INTO T_Base_SizeList");
		sql.append(" (szl_sz_code,szl_szg_code,szl_order,companyid)");
		sql.append(" VALUES(:szl_sz_code,:szl_szg_code,:szl_order,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(sizeList.toArray()));
	}

	@Override
	public void delGroup(Integer id) {
		StringBuffer sql = new StringBuffer("");
		T_Base_Size_Group size = queryGroupByID(id);
		sql.append(" DELETE FROM T_Base_SizeList");
		sql.append(" WHERE szl_szg_code=:szg_code");
		sql.append(" and companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(size));
		sql.setLength(0);
		sql.append(" DELETE FROM T_Base_Size_Group");
		sql.append(" WHERE szg_id=:szg_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("szg_id", id));
	}

	@Override
	public List<T_Base_Size_Group> listGroup(Map<String, Object> param) {
		Object name = param.get("name");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select szg_id,szg_code,szg_name");
		sql.append(" from t_base_size_group t");
		sql.append(" where 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" and instr(t.szg_name,:name)>0");
        }
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by szg_id asc");
		List<T_Base_Size_Group> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Size_Group.class));
		return list;
	}

	@Override
	public List<T_Base_SizeList> listByID(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select szl_id,szl_sz_code,sz_name,szl_order");
		sql.append(" from t_base_sizelist t");
		sql.append(" join t_base_size s");
		sql.append(" on sz_code=szl_sz_code");
		sql.append(" and s.companyid=t.companyid");
		sql.append(" where 1 = 1");
		sql.append(" and t.szl_szg_code=:szl_szg_code");
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by szl_order");
		List<T_Base_SizeList> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_SizeList.class));
		return list;
	}
	

	@Override
	public T_Base_Size queryByID(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select sz_id,sz_code,sz_name from T_Base_Size");
		sql.append(" where sz_id=:sz_id");
		try{
			T_Base_Size data = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("sz_id", id),new BeanPropertyRowMapper<>(T_Base_Size.class));
			return data;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public void save(T_Base_Size model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select f_two_code(max(sz_code+0)) from T_Base_Size ");
		sql.append(" where 1=1");
		sql.append(" and companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(model), String.class);
		model.setSz_code(code);
		sql.setLength(0);
		
		sql.append("INSERT INTO T_Base_Size");
		sql.append(" (sz_code,sz_name,companyid)");
		sql.append(" VALUES(:sz_code,:sz_name,:companyid)");
		
		KeyHolder holder = new GeneratedKeyHolder(); 
		
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model),holder);
		int id = holder.getKey().intValue();
		model.setSz_id(id);
	}

	@Override
	public void update(T_Base_Size model) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update T_Base_Size");
		sql.append(" set sz_name=:sz_name");
		sql.append(" where sz_id=:sz_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(model));
	}

	@Override
	public void del(Integer id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM T_Base_Size");
		sql.append(" WHERE sz_id=:sz_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sz_id", id));
	}

	@Override
	public List<T_Base_SizeList> listBySzg(List<String> szgCodes,Integer companyid) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("szg_code", szgCodes);
		params.put("companyid", companyid);
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT szg_code AS szl_szg_code,sz_code AS szl_sz_code,sz_name");
		sql.append(" FROM t_base_size_group szg");
		sql.append(" JOIN t_base_sizelist szl ON szg_code = szl_szg_code AND szg.companyid = szl.companyid");
		sql.append(" JOIN t_base_size sz ON sz_code = szl_sz_code AND sz.companyid = szl.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND szg_code IN(:szg_code)");
		sql.append(" AND szg.companyid = :companyid");
		sql.append(" ORDER BY szg_code,szl_order ASC");
		return namedParameterJdbcTemplate.query(sql.toString(), params, 
				new BeanPropertyRowMapper<>(T_Base_SizeList.class));
	}
	
	
}
